ajJSONToTable function
Available since AlchemyJ v4.1
Description
The ajJSONToTable function extracts node values from a JSON string and converts them into an array of data. When the selected nodes exist at different levels, it flattens the structure and this will be repeated for node values at a higher level.
Syntax
ajJSONToTable(table_column, json_data, [traversal_level], [Convert_to_text])
| Argument Name | Argument Type | Description |
|---|---|---|
| table_column (required) | Range | The range of cells (row only) in which the value in each cell represents a node name in the JSON string. The wording must be the same and it is case-sensitive. These node names can be at different levels in the JSON string. |
| json_data (required) | Range / Array | The range of cells that contains the JSON string. The range of cells can span across multiple rows and columns. The cell values will be concatenated into one string from left to right and top to bottom. |
| traversal_level (optional) | Double | Level of the path of the node should be searched from the JSON string. The top-level is 1. All the levels in the JSON string will be searched by default. |
| convert_to_text (optional) | Boolean | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE. |
The function will return:
1) Return Value: Populated JSON string in a range of cells
2) Return Type: Multiple values (array formula)
Examples
There is a JSON string as follows, "ShopInfo" is the node of the first layer, "Location" is the node of the second layer, "Product ID" is the node of the third layer.
{
"ShopInfo": {
"Location": "CausewayBay",
"Manager": "TomChan",
"Product": [
{
"Product ID": "P001",
"Product Name": "Xbox One",
"Price": 1000,
"On Sale": true
},
{
"Product ID": "P002",
"Product Name": "PS4",
"Price": 999.9,
"On Sale": false
}
],
"Tel": {
"AreaCode": "852",
"Phone": "94912390",
"Query": {
"Fax": "56175463",
"Phone": "94912399",
"Telex": "ALP-99197766"
}
}
}
}
Example 1
In this example, an array formula of ajJSONToTable from B9 to E9 matches node name in the JSON string by the specified column name(B1:E1) to extract node values into the range.
Since the ‘traversal_level’ is not defined, the function searches for the node name at all levels.
When you set Traversal_level to 2 which limits the node search to the second level only.
Note: If you want to convert the table back to the JSON string, you can use ajJSONFromTable to handle the single table .
Example 2
In this example, the values of table column are node of array object, ajJSONToTable will extract all data of this array object.
If the JSON string has the same key name at different layers, you need to use .. to define the column name.
Example 3
For the above JSON string, after converting to tables, it is difficult to convert tables to the same structure as the original JSON string. In this case, you need to use ajJSONKeyGeneration to add the level keys for JSON sting first.
{
"ShopInfo": {
"Location": "CausewayBay",
"Manager": "TomChan",
"Product": [
{
"Product ID": "P001",
"Product Name": "Xbox One",
"Price": 1000,
"On Sale": true,
"ajKeyProduct": "Product-1",
"ajKeyShopInfo": "ShopInfo-1"
},
{
"Product ID": "P002",
"Product Name": "PS4",
"Price": 999.9,
"On Sale": false,
"ajKeyProduct": "Product-2",
"ajKeyShopInfo": "ShopInfo-1"
},
{
"Product ID": "P003",
"Product Name": "Nintendo Switch",
"Price": 1500,
"On Sale": true,
"ajKeyProduct": "Product-3",
"ajKeyShopInfo": "ShopInfo-1"
}
],
"Tel": {
"AreaCode": "852",
"Phone": "94912390",
"Query": {
"Fax": "56175463",
"Telex": "ALP-99197766",
"ajKeyQuery": "Query-1",
"ajKeyTel": "Tel-1"
},
"ajKeyTel": "Tel-1",
"ajKeyShopInfo": "ShopInfo-1"
},
"ajKeyShopInfo": "ShopInfo-1",
"ajKeyTop": "Top-1"
},
"ajKeyTop": "Top-1"
}
Using ajJSONToTable extracts node values to tables.
Using Data Relationship Schema make the table data to JSON string and ajJSONFromSchema to shown it .
{ "ShopInfo": { "Location": "CausewayBay", "Manager": "TomChan", "ajKeyShopInfo": "ShopInfo-1", "ajKeyTop": "Top-1", "Product": [ { "Product ID": "P001", "Product Name": "Xbox One", "Price": 1000, "On Sale": true, "ajKeyProduct": "Product-1" }, { "Product ID": "P002", "Product Name": "PS4", "Price": 999.9, "On Sale": false, "ajKeyProduct": "Product-2" }, { "Product ID": "P003", "Product Name": "Nintendo Switch", "Price": 1500, "On Sale": true, "ajKeyProduct": "Product-3" } ], "Tel": { "AreaCode": "852", "Phone": "94912390", "ajKeyTel": "Tel-1", "Query": { "Fax": "56175463", "Telex": "ALP-99197766" } } } }
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| Invalid traversal level, it must be 0 or above. |
| Invalid JSON Format. |
| JSON data contains an Excel error. |